﻿IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_HO_SO_THAM_DINH_SearchByOwner]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sproc_HO_SO_THAM_DINH_SearchByOwner]
GO
 
CREATE PROCEDURE [dbo].sproc_HO_SO_THAM_DINH_SearchByOwner
	@USERID UNIQUEIDENTIFIER,
	@TRANGTHAI CHAR(1), /* Y N T*/
	@PageSize INT,
	@PageNo INT -- di tu 1
AS
BEGIN
	
	DECLARE @FromIndex INT
	DECLARE @ToIndex INT
	-- di tu 1
	SET @FromIndex = @PageSize * (@PageNo - 1) + 1
	SET @ToIndex = @PageSize * @PageNo
			   
	SELECT 
		RowNumber ,
		RowTotal,
		MA_HO_SO,
		DUONG,
		DOAN_DUONG,
		TEN_QUAN,
		TEN_PHUONG,
		SO_PHIEU,
		TEN_CHU_SU_DUNG,
		DIA_CHI,
		NGAY_KHOI_TAO,
		NGAY_CAP_NHAT,
		UserName,
		GIA_DAT,
		GIA_GIAO_DICH_TRIEU_DONG,
		GIA_DAT_1,
		GIA_DAT_2,
		GIA_DAT_BINH_QUAN_1,
		GIA_DAT_BINH_QUAN_2,
		ACTIVE_FLAG
	FROM (
		SELECT ROW_NUMBER() OVER(ORDER BY hstd.SO_PHIEU,hstd.NGAY_CAP_NHAT DESC) AS RowNumber,
		       COUNT(0) OVER() AS RowTotal,
		       hstd.MA_HO_SO,
		       hstd.SO_PHIEU,
		       dks.DUONG,
		       dks.DOAN_DUONG,
		       tsqquan.NOI_DUNG AS TEN_QUAN,
		       tsphuong.NOI_DUNG AS TEN_PHUONG,
		       hstd.TEN_CHU_SU_DUNG,
		       hstd.DIA_CHI,
		       hstd.NGAY_KHOI_TAO,
		       hstd.NGAY_CAP_NHAT,
				au.UserName,
		       gdd.GIA_DAT,
		       gtd.GIA_GIAO_DICH_TRIEU_DONG,
		       hstd.GIA_DAT_1,
		       hstd.GIA_DAT_2,
		       hstd.GIA_DAT_BINH_QUAN_1,
		       hstd.GIA_DAT_BINH_QUAN_2,
		       hstd.ACTIVE_FLAG
		FROM   HO_SO_THAM_DINH AS hstd
		       INNER JOIN DUONG_KHAO_SAT AS dks
		            ON hstd.MA_DKS = dks.MA_DKS
		       INNER JOIN THAM_SO tsqquan
		            ON dks.QUAN = tsqquan.MA_THAM_SO
		       INNER JOIN THAM_SO AS tsphuong
		            ON dks.PHUONG = tsphuong.MA_THAM_SO
		        INNER JOIN aspnet_Users au
					ON hstd.NGUOI_KHOI_TAO = au.UserId
				INNER JOIN GIA_THAM_DINH gtd
					ON gtd.MA_HO_SO = hstd.MA_HO_SO
		        LEFT JOIN GIA_DOAN_DUONG gdd
					ON gdd.MA_DKS = dks.MA_DKS
		WHERE 
				hstd.NGUOI_KHOI_TAO
				= 
				CASE WHEN @USERID IS NOT NULL THEN @USERID ELSE hstd.NGUOI_KHOI_TAO END
			AND
				 isnull(hstd.ACTIVE_FLAG,'')
				= 
				CASE WHEN @TRANGTHAI IS NOT NULL AND @TRANGTHAI <> '' THEN @TRANGTHAI ELSE isnull(hstd.ACTIVE_FLAG,'Y') END				
			AND hstd.ACTIVE_FLAG <> 'N'
			AND ( gdd.NGAY_CAP_NHAT
				=
				(SELECT MAX(NGAY_CAP_NHAT) FROM GIA_DOAN_DUONG gdd2 WHERE gdd2.MA_DKS = dks.MA_DKS)
				OR gdd.NGAY_CAP_NHAT IS NULL)
	)  AS rownumber
	WHERE RowNumber BETWEEN @FromIndex AND @ToIndex OR @PageNo = 0
END
GO 